package com.wxtf.platform.main.proprietaryInquires;

import java.io.File;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.wxtf.platform.interfaces.CommonsCallProvider;
import com.wxtf.platform.plugins.user.UserBean;
import com.wxtf.platform.supers.LogicBase;
import com.wxtf.platform.tools.Common;
import com.wxtf.platform.tools.ConstantInfoHelper;
import com.wxtf.platform.tools.AccessDataBaseUtil;
import com.wxtf.platform.tools.FormatUtil;
import com.wxtf.platform.tools.XLSUtils;
/**
 * <li>Title: ChildrenQuery.java</li>
 * <li>Project: baseplatform</li>
 * <li>Package: com.wxtf.platform.main.proprietaryInquires</li>
 * <li>Description: 本月新生儿名单</li>
 * <li>Copyright: Copyright (c) 2012</li>
 * <li>Company: wxthtf Technologies </li>
 * <li>Created on Sep 21, 2012 10:29:02 AM</li>
 * 
 * @author wangzhi
 * @version 1.0.0.0
 * 
 */
public class ChildrenQuery extends LogicBase implements CommonsCallProvider{
	private static final Log log = LogFactory.getLog(ChildrenQuery.class);

	public ChildrenQuery(String jsonArray){

	}
	
	@Override
	public Object run() {
		Object msg = "";
		try {
			if("queryPage".equals(this.getMethodName())) {
				msg = this.select(this.getLogicData());
				return msg;
			} else if("huizong".equals(this.getMethodName())) {
				msg = this.huizong(this.getLogicData());
				return msg;
			} else if("download".equals(this.getMethodName())) {
				msg = this.download(this.getLogicData());
			} else {
				msg = "\"没有【" + this.getMethodName() + "】该方法\"";
			}
			if(msg == null || "[null]".equals(msg)) {
				msg = "\"\"";
			}
			if(msg.toString().startsWith("{") || msg.toString().startsWith("[")){
				return msg;
			}
			msg = ConstantInfoHelper.generateJSONString(msg.toString());
		} catch(Exception e) {
			msg = ConstantInfoHelper.generateErrorJSONString();
			log.error(e, e);
		} finally {
			this.clear();
		}
		return msg.toString();
	}

	String[] fields = {
			"tbenm_id","tbenm_birth_time","tbenm_plan_status","tbenm_gravida_num","tbenm_gravida_times","tbe_name","reserve1","tbfi_spouse_name","tbfi_spouse_department"
	};
	@Override
	public Object select(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String type= jsonObject.getString("type");
		String curTime = FormatUtil.getCurentDate();//获取当前时间
		String []time_now=curTime.split("-");
		String sql=" from tbe_neonate_main t1 left join tbe_pregnancy_record rec on rec.tbepr_id=t1.tbepr_id left join tb_family_base f on f.tbfi_id=rec.tbfi_id left join tb_employee e on e.tbe_id=f.tbe_id where e.tbe_status not in ('102-3','102-4','102-5') and year(t1.tbenm_birth_time)="+time_now[0]+" and month(t1.tbenm_birth_time)="+time_now[1];
		if("init".equals(type)){
			sql = sql+" and e.org_id in (" + user.getCurSubOrgId() + ") ";	
		} else if("tj_ser".equals(type)) {
			String ser_from= jsonObject.getString("ser_from");
			String ser_to = jsonObject.getString("ser_to");
			String orgIds = jsonObject.getString("ser_org");
			String ser_plan_status = jsonObject.getString("ser_plan_status");//计划状态（一胎/二胎）
			String ser_late = jsonObject.getString("ser_late");//晚婚/晚育
			
			if(StringUtils.isBlank(orgIds)){
				sql=sql+" and e.org_id in ("+user.getCurSubOrgId()+") ";
			} else {
				sql=sql+" and e.org_id in ("+user.getCurSubOrgIds(orgIds)+") ";
			}
			if(!StringUtils.isBlank(ser_from)){
				sql=sql+" and t1.tbenm_birth_time >= '"+ser_from+"' ";
			}
			if(!StringUtils.isBlank(ser_to)){
				sql=sql+" and t1.tbenm_birth_time <= '"+ser_to+"' ";
			}
			if(!StringUtils.isBlank(ser_plan_status)){
				sql=sql+" and t1.tbenm_plan_status='"+ser_plan_status+"' ";
			}
			if(!StringUtils.isBlank(ser_late)){           
				sql=sql+" and t1.reserve4='1' ";
			}
			
		}
		// 排序
		String sortname = this.getRequest().getParameter("sortname");
		String sortorder = this.getRequest().getParameter("sortorder");
		if(sortname!=null&&sortname!=null) {
			sql += " order by " + sortname + " " + sortorder;
		}
		String countSql = "select count(*) " + sql;
		String querySql = "select t1.tbenm_id,t1.tbenm_birth_time,t1.tbenm_plan_status,t1.tbenm_gravida_num,t1.tbenm_gravida_times,e.tbe_name,e.reserve1,f.tbfi_spouse_name,f.tbfi_spouse_department " + sql;
		return AccessDataBaseUtil.query4SQLPagging(fields, countSql, querySql, this.getPage(), this.getPagesize(), null);
	}

	

	@Override
	public Object insert(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object update(Object jsonArray) throws Exception {
		return null;
	}
	
	@Override
	public Object delete(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object recordCount(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object setRequest(HttpServletRequest request) throws Exception {
		this.request = request;
		return request;
	}

	@Override
	public Object updateBatch(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object onCreate(Object jsonArray) {
		return null;
	}

	@Override
	public Object onDestory(Object jsonArray) {
		return null;
	}

	@Override
	public Object onInit(Object jsonArray) {
		return null;
	}
	
	//判断org_id是否是1-2级
	public static boolean levelJudgment(String orgID) throws Exception
	{
		String sql = "select levels from t_department where department_id = ?";
		List<Object> list = AccessDataBaseUtil.query4List(sql, new Object[]{ orgID });
		int levels = Integer.valueOf(String.valueOf(list.get(0)));
		if(levels<2) {return false;}
		else {return true;}
	}

	
	public Object huizong (Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		String curTime = FormatUtil.getCurentDate();//获取当前时间
		String []time_now=curTime.split("-");
		StringBuffer sb = new StringBuffer();
		String lll=user.getCurSubOrgId();
		String[] org=lll.split(",");

		//*********************************
		String sql_num="select count(*) from t_department where levels>=2 and department_id in ("+lll+")";
		int row_num=AccessDataBaseUtil.queryForInt(sql_num, null); //得到非0,1级机构的条数
		String [] org_fin=new String[row_num];//最后得到的机构数组
		String sql="";
		int jishu=0;
		for(int i=0;i<org.length;i++)
		{
			sql="select levels from t_department where department_id="+org[i];
			int lev=Integer.valueOf( AccessDataBaseUtil.query4List(sql, null).toString().replace("[", "").replace("]", "").replace(" ", "")).intValue();
			if(lev>=2)
			{
				org_fin[jishu]=org[i];
				jishu++;
			}
		}
		//*********************************
		
		sb.append("{\"Total\":\""+row_num+"\",\"OrgName\":\""+user.getCurOrgName()+"\",\"Rows\":[");
		for(int i = 0; i < org_fin.length; i++) {
			if(i!=0)
			{
				sb.append(",");
			}
			String sql_1="select sum(tbenm_gravida_num) from tbe_neonate_main t1 left join tbe_pregnancy_record rec on rec.tbepr_id=t1.tbepr_id left join tb_family_base f on f.tbfi_id=rec.tbfi_id left join tb_employee e on e.tbe_id=f.tbe_id where e.tbe_status not in ('102-3','102-4','102-5') and year(t1.tbenm_birth_time)="+time_now[0]+" and month(t1.tbenm_birth_time)='01' and e.org_id="+org_fin[i]+" ";
			String sql_2="select sum(tbenm_gravida_num) from tbe_neonate_main t1 left join tbe_pregnancy_record rec on rec.tbepr_id=t1.tbepr_id left join tb_family_base f on f.tbfi_id=rec.tbfi_id left join tb_employee e on e.tbe_id=f.tbe_id where e.tbe_status not in ('102-3','102-4','102-5') and year(t1.tbenm_birth_time)="+time_now[0]+" and month(t1.tbenm_birth_time)='02' and e.org_id="+org_fin[i]+" ";
			String sql_3="select sum(tbenm_gravida_num) from tbe_neonate_main t1 left join tbe_pregnancy_record rec on rec.tbepr_id=t1.tbepr_id left join tb_family_base f on f.tbfi_id=rec.tbfi_id left join tb_employee e on e.tbe_id=f.tbe_id where e.tbe_status not in ('102-3','102-4','102-5') and year(t1.tbenm_birth_time)="+time_now[0]+" and month(t1.tbenm_birth_time)='03' and e.org_id="+org_fin[i]+" ";
			String sql_4="select sum(tbenm_gravida_num) from tbe_neonate_main t1 left join tbe_pregnancy_record rec on rec.tbepr_id=t1.tbepr_id left join tb_family_base f on f.tbfi_id=rec.tbfi_id left join tb_employee e on e.tbe_id=f.tbe_id where e.tbe_status not in ('102-3','102-4','102-5') and year(t1.tbenm_birth_time)="+time_now[0]+" and month(t1.tbenm_birth_time)='04' and e.org_id="+org_fin[i]+" ";
			String sql_5="select sum(tbenm_gravida_num) from tbe_neonate_main t1 left join tbe_pregnancy_record rec on rec.tbepr_id=t1.tbepr_id left join tb_family_base f on f.tbfi_id=rec.tbfi_id left join tb_employee e on e.tbe_id=f.tbe_id where e.tbe_status not in ('102-3','102-4','102-5') and year(t1.tbenm_birth_time)="+time_now[0]+" and month(t1.tbenm_birth_time)='05' and e.org_id="+org_fin[i]+" ";
			String sql_6="select sum(tbenm_gravida_num) from tbe_neonate_main t1 left join tbe_pregnancy_record rec on rec.tbepr_id=t1.tbepr_id left join tb_family_base f on f.tbfi_id=rec.tbfi_id left join tb_employee e on e.tbe_id=f.tbe_id where e.tbe_status not in ('102-3','102-4','102-5') and year(t1.tbenm_birth_time)="+time_now[0]+" and month(t1.tbenm_birth_time)='06' and e.org_id="+org_fin[i]+" ";
			String sql_7="select sum(tbenm_gravida_num) from tbe_neonate_main t1 left join tbe_pregnancy_record rec on rec.tbepr_id=t1.tbepr_id left join tb_family_base f on f.tbfi_id=rec.tbfi_id left join tb_employee e on e.tbe_id=f.tbe_id where e.tbe_status not in ('102-3','102-4','102-5') and year(t1.tbenm_birth_time)="+time_now[0]+" and month(t1.tbenm_birth_time)='07' and e.org_id="+org_fin[i]+" ";
			String sql_8="select sum(tbenm_gravida_num) from tbe_neonate_main t1 left join tbe_pregnancy_record rec on rec.tbepr_id=t1.tbepr_id left join tb_family_base f on f.tbfi_id=rec.tbfi_id left join tb_employee e on e.tbe_id=f.tbe_id where e.tbe_status not in ('102-3','102-4','102-5') and year(t1.tbenm_birth_time)="+time_now[0]+" and month(t1.tbenm_birth_time)='08' and e.org_id="+org_fin[i]+" ";
			String sql_9="select sum(tbenm_gravida_num) from tbe_neonate_main t1 left join tbe_pregnancy_record rec on rec.tbepr_id=t1.tbepr_id left join tb_family_base f on f.tbfi_id=rec.tbfi_id left join tb_employee e on e.tbe_id=f.tbe_id where e.tbe_status not in ('102-3','102-4','102-5') and year(t1.tbenm_birth_time)="+time_now[0]+" and month(t1.tbenm_birth_time)='09' and e.org_id="+org_fin[i]+" ";
			String sql_10="select sum(tbenm_gravida_num) from tbe_neonate_main t1 left join tbe_pregnancy_record rec on rec.tbepr_id=t1.tbepr_id left join tb_family_base f on f.tbfi_id=rec.tbfi_id left join tb_employee e on e.tbe_id=f.tbe_id where e.tbe_status not in ('102-3','102-4','102-5') and year(t1.tbenm_birth_time)="+time_now[0]+" and month(t1.tbenm_birth_time)='10' and e.org_id="+org_fin[i]+" ";
			String sql_11="select sum(tbenm_gravida_num) from tbe_neonate_main t1 left join tbe_pregnancy_record rec on rec.tbepr_id=t1.tbepr_id left join tb_family_base f on f.tbfi_id=rec.tbfi_id left join tb_employee e on e.tbe_id=f.tbe_id where e.tbe_status not in ('102-3','102-4','102-5') and year(t1.tbenm_birth_time)="+time_now[0]+" and month(t1.tbenm_birth_time)='11' and e.org_id="+org_fin[i]+" ";
			String sql_12="select sum(tbenm_gravida_num) from tbe_neonate_main t1 left join tbe_pregnancy_record rec on rec.tbepr_id=t1.tbepr_id left join tb_family_base f on f.tbfi_id=rec.tbfi_id left join tb_employee e on e.tbe_id=f.tbe_id where e.tbe_status not in ('102-3','102-4','102-5') and year(t1.tbenm_birth_time)="+time_now[0]+" and month(t1.tbenm_birth_time)='12' and e.org_id="+org_fin[i]+" ";
			String sql_sum="select sum(tbenm_gravida_num) from tbe_neonate_main t1 left join tbe_pregnancy_record rec on rec.tbepr_id=t1.tbepr_id left join tb_family_base f on f.tbfi_id=rec.tbfi_id left join tb_employee e on e.tbe_id=f.tbe_id where e.tbe_status not in ('102-3','102-4','102-5') and year(t1.tbenm_birth_time)="+time_now[0]+"  and e.org_id="+org_fin[i]+" ";
			//sb.append("{\"name1\":\""+find_orgName(org_fin[i])+"\", \"name2\":\""+AccessDataBaseUtil.queryForInt(sql_1, null)+"\", \"name3\": \""+AccessDataBaseUtil.queryForInt(sql_2, null)+"\", \"name4\": \""+AccessDataBaseUtil.queryForInt(sql_3, null)+"\", \"name5\":\""+AccessDataBaseUtil.queryForInt(sql_4, null)+"\", \"name6\": \""+AccessDataBaseUtil.queryForInt(sql_5, null)+"\", \"name7\":\""+AccessDataBaseUtil.queryForInt(sql_6, null)+"\", \"name8\":\""+AccessDataBaseUtil.queryForInt(sql_7, null)+"\", \"name9\":\""+AccessDataBaseUtil.queryForInt(sql_8, null)+"\", \"name10\":\""+AccessDataBaseUtil.queryForInt(sql_9, null)+"\", \"name11\":\""+AccessDataBaseUtil.queryForInt(sql_10, null)+"\", \"name12\":\""+AccessDataBaseUtil.queryForInt(sql_11, null)+"\", \"name13\":\""+AccessDataBaseUtil.queryForInt(sql_12, null)+"\",\"name14\":\""+AccessDataBaseUtil.queryForInt(sql_sum, null)+"\"}");
			sb.append("{\"name1\":\""+find_orgName(org_fin[i])+"\", \"name2\":\""+sum_sql(sql_1)+"\", \"name3\": \""+sum_sql(sql_2)+"\", \"name4\": \""+sum_sql(sql_3)+"\", \"name5\":\""+sum_sql(sql_4)+"\", \"name6\": \""+sum_sql(sql_5)+"\", \"name7\":\""+sum_sql(sql_6)+"\", \"name8\":\""+sum_sql(sql_7)+"\", \"name9\":\""+sum_sql(sql_8)+"\", \"name10\":\""+sum_sql(sql_9)+"\", \"name11\":\""+sum_sql(sql_10)+"\", \"name12\":\""+sum_sql(sql_11)+"\", \"name13\":\""+sum_sql(sql_12)+"\",\"name14\":\""+sum_sql(sql_sum)+"\"}");
		}	
		String sql_heji="select sum(tbenm_gravida_num) from tbe_neonate_main t1 left join tbe_pregnancy_record rec on rec.tbepr_id=t1.tbepr_id left join tb_family_base f on f.tbfi_id=rec.tbfi_id left join tb_employee e on e.tbe_id=f.tbe_id  where e.tbe_status not in ('102-3','102-4','102-5') and year(t1.tbenm_birth_time)="+time_now[0]+" and e.org_id in ("+user.getCurSubOrgId()+") ";
		
		sb.append("],\"Heji\":\"").append(sum_sql(sql_heji)).append("\"}");
		return sb.toString();
	}
	//执行sql，返回和值
	public String sum_sql(String sql) throws Exception {
		String fin_sum=AccessDataBaseUtil.query4List(sql, null).toString().replace("[", "").replace("]", "");
		if("".equals(fin_sum))
		{fin_sum="0"; }
		return fin_sum;
	}
	//求组织机构名
	public String find_orgName(String org_id) throws Exception {
		String orgId = org_id;
		String sql = "select caption from t_department where department_id=" + orgId + " ";
		String val_org_name = AccessDataBaseUtil.query4List(sql, null).toString().replace("[", "").replace("]", "");
		return val_org_name;
	}
	
	
	
	/**
	 * 
	 * 描述：下载报表
	 * @param jsonArray
	 * @return Object
	 * @throws Exception
	 * @CreateOn Sep 27, 2012 16:03:36 PM
	 * @author wangzhi
	 */
	public Object download(String jsonArray) throws Exception {
		//JSONArray array = JSONArray.fromObject(jsonArray);
		//StringBuffer sb = new StringBuffer();
		
		List<Object[]> list = new ArrayList<Object[]>();
		String curTime = FormatUtil.getCurentDate();//获取当前时间
		String []time_now=curTime.split("-");
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		String lll=user.getCurSubOrgId();
		String[] org=lll.split(",");
		//*********************************
		String sql_num="select count(*) from t_department where levels>=2 and department_id in ("+lll+")";
		int row_num=AccessDataBaseUtil.queryForInt(sql_num, null); //得到非0,1级机构的条数
		String [] org_fin=new String[row_num];//最后得到的机构数组
		String sql="";
		int jishu=0;
		for(int i=0;i<org.length;i++)
		{
			sql="select levels from t_department where department_id="+org[i];
			int lev=Integer.valueOf( AccessDataBaseUtil.query4List(sql, null).toString().replace("[", "").replace("]", "").replace(" ", "")).intValue();
			if(lev>=2)
			{
				org_fin[jishu]=org[i];
				jishu++;
			}
		}
		//*********************************
		for(int i=0;i<org_fin.length;i++)
		{
			String sql_1="select sum(tbenm_gravida_num) from tbe_neonate_main t1 left join tbe_pregnancy_record rec on rec.tbepr_id=t1.tbepr_id left join tb_family_base f on f.tbfi_id=rec.tbfi_id left join tb_employee e on e.tbe_id=f.tbe_id where e.tbe_status not in ('102-3','102-4','102-5') and year(t1.tbenm_birth_time)="+time_now[0]+" and month(t1.tbenm_birth_time)='01' and e.org_id="+org_fin[i]+" ";
			String sql_2="select sum(tbenm_gravida_num) from tbe_neonate_main t1 left join tbe_pregnancy_record rec on rec.tbepr_id=t1.tbepr_id left join tb_family_base f on f.tbfi_id=rec.tbfi_id left join tb_employee e on e.tbe_id=f.tbe_id where e.tbe_status not in ('102-3','102-4','102-5') and year(t1.tbenm_birth_time)="+time_now[0]+" and month(t1.tbenm_birth_time)='02' and e.org_id="+org_fin[i]+" ";
			String sql_3="select sum(tbenm_gravida_num) from tbe_neonate_main t1 left join tbe_pregnancy_record rec on rec.tbepr_id=t1.tbepr_id left join tb_family_base f on f.tbfi_id=rec.tbfi_id left join tb_employee e on e.tbe_id=f.tbe_id where e.tbe_status not in ('102-3','102-4','102-5') and year(t1.tbenm_birth_time)="+time_now[0]+" and month(t1.tbenm_birth_time)='03' and e.org_id="+org_fin[i]+" ";
			String sql_4="select sum(tbenm_gravida_num) from tbe_neonate_main t1 left join tbe_pregnancy_record rec on rec.tbepr_id=t1.tbepr_id left join tb_family_base f on f.tbfi_id=rec.tbfi_id left join tb_employee e on e.tbe_id=f.tbe_id where e.tbe_status not in ('102-3','102-4','102-5') and year(t1.tbenm_birth_time)="+time_now[0]+" and month(t1.tbenm_birth_time)='04' and e.org_id="+org_fin[i]+" ";
			String sql_5="select sum(tbenm_gravida_num) from tbe_neonate_main t1 left join tbe_pregnancy_record rec on rec.tbepr_id=t1.tbepr_id left join tb_family_base f on f.tbfi_id=rec.tbfi_id left join tb_employee e on e.tbe_id=f.tbe_id where e.tbe_status not in ('102-3','102-4','102-5') and year(t1.tbenm_birth_time)="+time_now[0]+" and month(t1.tbenm_birth_time)='05' and e.org_id="+org_fin[i]+" ";
			String sql_6="select sum(tbenm_gravida_num) from tbe_neonate_main t1 left join tbe_pregnancy_record rec on rec.tbepr_id=t1.tbepr_id left join tb_family_base f on f.tbfi_id=rec.tbfi_id left join tb_employee e on e.tbe_id=f.tbe_id where e.tbe_status not in ('102-3','102-4','102-5') and year(t1.tbenm_birth_time)="+time_now[0]+" and month(t1.tbenm_birth_time)='06' and e.org_id="+org_fin[i]+" ";
			String sql_7="select sum(tbenm_gravida_num) from tbe_neonate_main t1 left join tbe_pregnancy_record rec on rec.tbepr_id=t1.tbepr_id left join tb_family_base f on f.tbfi_id=rec.tbfi_id left join tb_employee e on e.tbe_id=f.tbe_id where e.tbe_status not in ('102-3','102-4','102-5') and year(t1.tbenm_birth_time)="+time_now[0]+" and month(t1.tbenm_birth_time)='07' and e.org_id="+org_fin[i]+" ";
			String sql_8="select sum(tbenm_gravida_num) from tbe_neonate_main t1 left join tbe_pregnancy_record rec on rec.tbepr_id=t1.tbepr_id left join tb_family_base f on f.tbfi_id=rec.tbfi_id left join tb_employee e on e.tbe_id=f.tbe_id where e.tbe_status not in ('102-3','102-4','102-5') and year(t1.tbenm_birth_time)="+time_now[0]+" and month(t1.tbenm_birth_time)='08' and e.org_id="+org_fin[i]+" ";
			String sql_9="select sum(tbenm_gravida_num) from tbe_neonate_main t1 left join tbe_pregnancy_record rec on rec.tbepr_id=t1.tbepr_id left join tb_family_base f on f.tbfi_id=rec.tbfi_id left join tb_employee e on e.tbe_id=f.tbe_id where e.tbe_status not in ('102-3','102-4','102-5') and year(t1.tbenm_birth_time)="+time_now[0]+" and month(t1.tbenm_birth_time)='09' and e.org_id="+org_fin[i]+" ";
			String sql_10="select sum(tbenm_gravida_num) from tbe_neonate_main t1 left join tbe_pregnancy_record rec on rec.tbepr_id=t1.tbepr_id left join tb_family_base f on f.tbfi_id=rec.tbfi_id left join tb_employee e on e.tbe_id=f.tbe_id where e.tbe_status not in ('102-3','102-4','102-5') and year(t1.tbenm_birth_time)="+time_now[0]+" and month(t1.tbenm_birth_time)='10' and e.org_id="+org_fin[i]+" ";
			String sql_11="select sum(tbenm_gravida_num) from tbe_neonate_main t1 left join tbe_pregnancy_record rec on rec.tbepr_id=t1.tbepr_id left join tb_family_base f on f.tbfi_id=rec.tbfi_id left join tb_employee e on e.tbe_id=f.tbe_id where e.tbe_status not in ('102-3','102-4','102-5') and year(t1.tbenm_birth_time)="+time_now[0]+" and month(t1.tbenm_birth_time)='11' and e.org_id="+org_fin[i]+" ";
			String sql_12="select sum(tbenm_gravida_num) from tbe_neonate_main t1 left join tbe_pregnancy_record rec on rec.tbepr_id=t1.tbepr_id left join tb_family_base f on f.tbfi_id=rec.tbfi_id left join tb_employee e on e.tbe_id=f.tbe_id where e.tbe_status not in ('102-3','102-4','102-5') and year(t1.tbenm_birth_time)="+time_now[0]+" and month(t1.tbenm_birth_time)='12' and e.org_id="+org_fin[i]+" ";
			String sql_sum="select sum(tbenm_gravida_num) from tbe_neonate_main t1 left join tbe_pregnancy_record rec on rec.tbepr_id=t1.tbepr_id left join tb_family_base f on f.tbfi_id=rec.tbfi_id left join tb_employee e on e.tbe_id=f.tbe_id where e.tbe_status not in ('102-3','102-4','102-5') and year(t1.tbenm_birth_time)="+time_now[0]+"  and e.org_id="+org_fin[i]+" ";
			//String[] arr ={find_orgName(org_fin[i]),String.valueOf(AccessDataBaseUtil.queryForInt(sql_1, null)),String.valueOf(AccessDataBaseUtil.queryForInt(sql_2, null)),String.valueOf(AccessDataBaseUtil.queryForInt(sql_3, null)),String.valueOf(AccessDataBaseUtil.queryForInt(sql_4, null)),String.valueOf(AccessDataBaseUtil.queryForInt(sql_5, null)),String.valueOf(AccessDataBaseUtil.queryForInt(sql_6, null)),String.valueOf(AccessDataBaseUtil.queryForInt(sql_7, null)),String.valueOf(AccessDataBaseUtil.queryForInt(sql_8, null)),String.valueOf(AccessDataBaseUtil.queryForInt(sql_9, null)),String.valueOf(AccessDataBaseUtil.queryForInt(sql_10, null)),String.valueOf(AccessDataBaseUtil.queryForInt(sql_11, null)),String.valueOf(AccessDataBaseUtil.queryForInt(sql_12, null)),String.valueOf(AccessDataBaseUtil.queryForInt(sql_sum, null))};
			String[] arr ={find_orgName(org_fin[i]),sum_sql(sql_1),sum_sql(sql_2),sum_sql(sql_3),sum_sql(sql_4),sum_sql(sql_5),sum_sql(sql_6),sum_sql(sql_7),sum_sql(sql_8),sum_sql(sql_9),sum_sql(sql_10),sum_sql(sql_11),sum_sql(sql_12),sum_sql(sql_sum)};
			list.add(arr);
		}
		String url = this.getRequest().getSession().getServletContext().getRealPath("/jsp/app/xls/");
		String model = url + "/model/children_newadd.xls";
		String file = url + "/file/children_newadd.xls";
		String rfile = "jsp/app/xls/file/children_newadd.xls";
		File ffrom = new File(model);
		File fto = new File(file);
		Common.copyFile(ffrom, fto);

		if(list.size() > 0){
			XLSUtils.writeXls(list, fto, "新生儿人数汇总", 4);//写xls内容
		} else {
			return "{\"result\":false,\"resultmessage\":\"调用失败\",\"resultrecord\":\"导出结果集没有记录\"}";
		}
		return "\"" + rfile + "\"";
		
	}
	
}


